package com.guo.core.framework.suanfa;

import cn.hutool.core.lang.Console;
import cn.hutool.core.map.MapUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.sun.org.apache.bcel.internal.generic.IF_ACMPEQ;
import lombok.val;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.junit.Test;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.security.Key;
import java.sql.SQLException;
import java.sql.SQLOutput;
import java.util.*;
import java.util.stream.Collectors;

public class SuanFaTest {

    public static final String SRC = "E:/file/work/调度/需求交付/算法回测/src/";
    public static final String DIST = "E:/file/work/调度/需求交付/算法回测/dist/";
    public static final String OUT_EXCEL = "E:/file/work/调度/需求交付/算法回测/结果.xlsx";
    // 16 21 25 28 30
    public static final String SRC_FILE = "1003.xlsx";
    public static final String DIST_FILE = "1003.xlsx";

    public static final String SELECT_ONE = "select id from suanfa_result where task_no=?";
    public static final String BOP_SELECT_ONE = "select * from bop_help_table where bop_url=?";

    public static final String BIZ_NO = "20221105TEST0001";
    public static final String QISHI_CANG = "广东省佛山市三水区";

    public static final String TO = " -> ";

    public static final String FENZU_LIST = "select\n" +
        "\ttask_no as taskNo,\n" +
        "\ttask_cost as taskCost,\n" +
        "\treceiver_detail_addr as receiverDetailAddr,\n" +
        "\treceiver_province_name as receiverProvinceName,\n" +
        "\treceiver_city_name as receiverCityName,\n" +
        "\treceiver_district_name as receiverDistrictName,\n" +
        "\tlf_cost as lfCost,\n" +
        "\tlfgl_cost as lfglCost,\n" +
        "\tline_sum_cost as lineSumCost,\n" +
        "\tmileage as mileage,\n" +
        "\tcustomer_no as customerNo,\n" +
        "\tcrosspoint as crosspoint,\n" +
        "\tcrosspoint_sort as crosspointSort,\n" +
        "\tline_volume as lineVolume,\n" +
        "\tline_rate as lineRate,\n" +
        "\tsender_detail_addr as senderDetailAddr,\n" +
        "\tline_max_load as lineMaxLoad,\n" +
        "\ttask_volume as taskVolume,\n" +
        "\tcar_type as carType,\n" +
        "\tcar_num as carNum,\n" +
        "\tbatch_no as batchNo,\n" +
        "\tbiz_no as bizNo\n" +
        "from\n" +
        "\tsuanfa_result\n";
    //        +
//        "limit 20";
    public static final String BOP_LIST = "select \n" +
        "id AS id,\n" +
        "system_name AS systemName,\n" +
        "service_name AS serviceName,\n" +
        "interface_trace AS interfaceTrace,\n" +
        "class_name AS className,\n" +
        "method_name AS methodName,\n" +
        "interface_type AS interfaceType,\n" +
        "bop_url AS bopUrl,\n" +
        "business_interface_name AS businessInterfaceName,\n" +
        "source_system AS sourceSystem,\n" +
        "source_system_name AS sourceSystemName,\n" +
        "direction AS direction,\n" +
        "bop_gateway_name AS bopGatewayName,\n" +
        "bop_interface_name AS bopInterfaceName,\n" +
        "target_url AS targetUrl,\n" +
        "target_url_type AS targetUrlType,\n" +
        "target_url_model AS targetUrlModel,\n" +
        "transport_type AS transportType,\n" +
        "active_condition AS activeCondition,\n" +
        "our_director AS our_director,\n" +
        "target_director AS targetDirector,\n" +
        "is_confirm AS isConfirm,\n" +
        "is_delete AS isDelete,\n" +
        "remark AS remark\n" +
        "from bop_help_table\n" +
        "where system_name =\"ZTB\"";

    @Test
    public void 导入原始数据() throws SQLException {
        this.inSrc(SRC.concat(SRC_FILE));
    }

    @Test
    public void 张灿插入数据() throws SQLException {
        String fileName = "C:/Users/zhangcan30/Desktop/零担下发EXCEL/数据库中的数据.csv";
        ExcelReader reader = ExcelUtil.getReader(fileName, 0);
        List<Map<String, Object>> readAll = reader.readAll();
        List<Entity> srcList = new ArrayList<>();

        for (int i = 0; i < readAll.size(); i++) {
            Map<String, Object> map = readAll.get(i);
            String system_name = "ZTB";
            String service_name = MapUtil.getStr(map, "服务名");
            String interface_trace = MapUtil.getStr(map, "接口路径");
            String class_name = MapUtil.getStr(map, "接口类名");
            String method_name = MapUtil.getStr(map, "方法名");
            String interface_type = MapUtil.getStr(map, "接口类型");
            String bop_url = MapUtil.getStr(map, "接口url");
            String business_interface_name = MapUtil.getStr(map, "业务接口名称");
            String source_system = MapUtil.getStr(map, "来源系统");
            String source_system_name = MapUtil.getStr(map, "来源系统名称");
            String direction = MapUtil.getStr(map, "方向");
            String bop_gateway_name = MapUtil.getStr(map, "鹊桥网关");
            String bop_interface_name = MapUtil.getStr(map, "鹊桥接口名称");
            String target_url = MapUtil.getStr(map, "接口地址");
            String target_url_type = MapUtil.getStr(map, "接口方式");
            String target_url_model = MapUtil.getStr(map, "接口模式");
            String transport_type = MapUtil.getStr(map, "传输方式");
            String active_condition = MapUtil.getStr(map, "触发条件");
            String our_director = MapUtil.getStr(map, "本系统责任人");
            String target_director = MapUtil.getStr(map, "对接系统责任人");
            String is_confirm = MapUtil.getStr(map, "是否已确认");
            String is_delete = MapUtil.getStr(map, "是否已废弃");
            String remark = MapUtil.getStr(map, "备注");

            System.out.println(interface_trace);
            Entity src = Entity.create("bop_help_table")
                .set("system_name", system_name)
                .set("service_name", service_name)
                .set("interface_trace", interface_trace)
                .set("class_name", class_name)
                .set("method_name", method_name)
                .set("interface_type", interface_type)
                .set("bop_url", bop_url)
                .set("business_interface_name", business_interface_name)
                .set("source_system", source_system)
                .set("source_system_name", source_system_name)
                .set("direction", direction)
                .set("bop_gateway_name", bop_gateway_name)
                .set("bop_interface_name", bop_interface_name)
                .set("target_url", target_url)
                .set("target_url_type", target_url_type)
                .set("target_url_model", target_url_model)
                .set("transport_type", transport_type)
                .set("active_condition", active_condition)
                .set("our_director", our_director)
                .set("target_director", target_director)
                .set("is_confirm", is_confirm)
                .set("is_delete", is_delete)
                .set("remark", remark);
            srcList.add(src);

        }
        System.out.println(srcList.size());
        Db.use("dev").insert(srcList);

    }

    @Test
    public void 张灿修改数据() throws SQLException {
        String fileName = "C:/Users/zhangcan30/Desktop/aaaaa/鹊桥生产网关接口信息.xlsx";
        ExcelReader reader = ExcelUtil.getReader(fileName, 0);
        List<Map<String, Object>> readAll = reader.readAll();
        List<Entity> srcList = new ArrayList<>();
        for (int i = 0; i < readAll.size(); i++) {
            Map<String, Object> map = readAll.get(i);
            String bopUrl = MapUtil.getStr(map, "鹊桥网关接口地址");
            String bop_url = bopUrl.substring(27);
            bop_url="/inner"+bop_url;
            //System.out.println(bop_url);
            //bop_url="/inner"+bop_url;

            List<Entity> entitys = Db.use("dev").query(BOP_SELECT_ONE,bop_url);
            if (CollectionUtils.isEmpty(entitys)){
                continue;
            }
            for (Entity entity : entitys) {
                Long id = entity.getLong("id");
                String gatewayName = MapUtil.getStr(map, "网关名称");
                String interfaceName = MapUtil.getStr(map, "网关接口名称");
                String url1 = MapUtil.getStr(map, "目标服务URL");
                String url2 = MapUtil.getStr(map, "目标服务");
                String url = url1 + url2;

                Entity update = Entity.create("bop_help_table")
                    .set("bop_gateway_name", gatewayName)
                    .set("bop_interface_name", interfaceName)
                    .set("target_url", url);
                Entity where = Entity.create("bop_help_table").set("id", id);
                Db.use("dev").update(update, where);
            }


        }
        System.out.println("aaaaaaaaaaaaaaa");
    }

    @Test
    public void 张灿导出数据() throws SQLException {
        List<BopDto> result = Db.use("dev").query(BOP_LIST, BopDto.class);
        ArrayList<Map<String,String>> list=new ArrayList<>();
        for (BopDto data : result) {
            Map<String ,String> map=new LinkedHashMap<>();
            map.put("服务名",data.getServiceName());
            map.put("接口路径",data.getInterfaceTrace());
            map.put("接口类名",data.getClassName());
            map.put("方法名",data.getMethodName());
            map.put("接口类型",data.getInterfaceType());
            map.put("接口url",data.getBopUrl());
            map.put("业务接口名称",data.getBusinessInterfaceName());
            map.put("来源系统",data.getSourceSystem());
            map.put("来源系统名称",data.getSourceSystemName());
            map.put("方向",data.getDirection());
            map.put("鹊桥网关",data.getBopGatewayName());
            map.put("鹊桥接口名称",data.getBopInterfaceName());
            map.put("接口地址",data.getTargetUrl());
            map.put("接口方式",data.getTargetUrlType());
            map.put("接口模式",data.getTargetUrlModel());
            map.put("传输方式",data.getTransportType());
            map.put("触发条件",data.getActiveCondition());
            map.put("本系统责任人",data.getOurDirector());
            map.put("对接系统责任人",data.getTargetDirector());
            map.put("是否已确认",data.getIsConfirm());
            map.put("是否已废弃",data.getIsDelete());
            map.put("备注",data.getRemark());
            list.add(map);
        }
        ZipSecureFile.setMinInflateRatio(-1.0d);
        ExcelWriter writer = new ExcelWriter("C:/Users/zhangcan30/Desktop/aaaaa/ZTB.xlsx");
        writer.write(list, true);
        writer.close();

    }

    private void inSrc(String srcFile) throws SQLException {
        ExcelReader reader = ExcelUtil.getReader(srcFile);
        List<Map<String, Object>> readAll = reader.readAll();
        List<Entity> srcList = new ArrayList<>();
        for (int i = 0; i < readAll.size(); i++) {
            Map<String, Object> map = readAll.get(i);
            String taskNo = MapUtil.getStr(map, "taskNo");
            if (StringUtils.isEmpty(taskNo)) {
                continue;
            }

            String senderDetailAddr = MapUtil.getStr(map, "senderDetailAddr");
            String receiverProvinceName = MapUtil.getStr(map, "receiverProvinceName");
            String receiverCityName = MapUtil.getStr(map, "receiverCityName");
            String receiverDistrictName = MapUtil.getStr(map, "receiverDistrictName");
            String receiverDetailAddr = MapUtil.getStr(map, "receiverDetailAddr");

            Integer batchNo = MapUtil.getInt(map, "batchNo");
            Entity src = Entity.create("suanfa_result")
                .set("biz_no", BIZ_NO)
                .set("task_no", taskNo)
                .set("batch_no", batchNo)
                .set("sender_detail_addr", senderDetailAddr)
                .set("receiver_detail_addr", receiverDetailAddr)
                .set("receiver_province_name", receiverProvinceName)
                .set("receiver_city_name", receiverCityName)
                .set("receiver_district_name", receiverDistrictName);
            srcList.add(src);
        }

        Db.use("dev").insert(srcList);
    }

    @Test
    public void 导入结果() throws SQLException {
        this.inResult(DIST.concat(DIST_FILE));
    }

    private void inResult(String dist) throws SQLException {
        ExcelReader reader = ExcelUtil.getReader(dist);
        List<Map<String, Object>> readAll = reader.readAll();
        for (int i = 0; i < readAll.size(); i++) {
            Map<String, Object> map = readAll.get(i);
            String taskNo = MapUtil.getStr(map, "线路运单号");
            if (StringUtils.isEmpty(taskNo)) {
                continue;
            }

            Entity entity = Db.use("dev").queryOne(SELECT_ONE, taskNo);
            if (entity == null) {
                continue;
            }

            Long id = entity.getLong("id");
            Integer batchNo = MapUtil.getInt(map, "批次");
            Entity update = Entity.create("suanfa_result")
                .set("task_cost", toBigDecimal(MapUtil.getStr(map, "运单成本")))
                .set("lf_cost", toBigDecimal(MapUtil.getStr(map, "预估路方成本")))
                .set("lfgl_cost", toBigDecimal(MapUtil.getStr(map, "预估线路方公里成本")))
                .set("line_sum_cost", toBigDecimal(MapUtil.getStr(map, "线路总成本")))
                .set("mileage", toBigDecimal(MapUtil.getStr(map, "线路里程")))
                .set("customer_no", MapUtil.getStr(map, "线路客户订单"))
                .set("crosspoint", MapUtil.getInt(map, "线路串点数"))
                .set("crosspoint_sort", i)
                .set("line_volume", toBigDecimal(MapUtil.getStr(map, "线路方量")))
                .set("line_rate", toBigDecimal(MapUtil.getStr(map, "线路装载率")))
                .set("line_max_load", toBigDecimal(MapUtil.getStr(map, "线路最大装载")))
                .set("task_volume", toBigDecimal(MapUtil.getStr(map, "运单方量")))
                .set("car_type", MapUtil.getStr(map, "车型"))
                .set("car_num", MapUtil.getInt(map, "车次"))

                .set("batch_no", MapUtil.getStr(map, "批次"));
            Entity where = Entity.create("suanfa_result").set("id", id);
            Db.use("dev").update(update, where);
        }
    }

    @Test
    public void 数据分析() throws SQLException {
        List<SuanFaResult> result = Db.use("dev").query(FENZU_LIST, SuanFaResult.class);
        Map<String, LineDto> lineMap = new HashMap<>();
        Map<String, RouteDto> routeMap = new HashMap<>();

        Map<String, List<SuanFaResult>> orderByResult = result.stream().filter(s -> s.getCrosspointSort() != null).collect(Collectors.groupingBy(m -> m.getBatchNo() + "_" + m.getCarNum()));
        for (Map.Entry<String, List<SuanFaResult>> entry : orderByResult.entrySet()) {
            String batchCar = entry.getKey();
            List<SuanFaResult> taskList = entry.getValue();
            taskList = taskList.stream().sorted(Comparator.comparing(SuanFaResult::getCrosspointSort)).collect(Collectors.toList());
            SuanFaResult max = taskList.get(taskList.size() - 1);

            String lineName = max.getReceiverProvinceName() + max.getReceiverCityName() + max.getReceiverDistrictName();
            this.addLine(lineMap, lineName);
            StringBuffer resultStr = new StringBuffer("批次:" + batchCar + "\n线路:" + lineName);

            // 节点路由
            List<String> routeNameList = new ArrayList<>();
            // 添加节点路由
            int size = taskList.size();
            for (int i = 0; i < size; i++) {
                SuanFaResult suanFaResult = taskList.get(i);
                String receiverDistrictName = suanFaResult.getReceiverProvinceName() + suanFaResult.getReceiverCityName() + suanFaResult.getReceiverDistrictName();
                routeNameList.add(receiverDistrictName);

                String routeName = "";
                if (i == 0) {
                    routeName = QISHI_CANG + TO + receiverDistrictName;
                } else {
                    SuanFaResult preSuanFaResult = taskList.get(i - 1);
                    routeName = preSuanFaResult.getReceiverProvinceName() + preSuanFaResult.getReceiverCityName() + preSuanFaResult.getReceiverDistrictName() + TO + receiverDistrictName;
                }
                resultStr.append("\n路由:" + suanFaResult.getCrosspointSort() + "/" + routeName);
                this.addRoute(routeMap, routeName, lineName);
            }

        }

        this.exportData(lineMap, routeMap);

    }

    @Test
    public void 新分析() throws SQLException {
        List<SuanFaResult> result = Db.use("dev").query(FENZU_LIST, SuanFaResult.class);
        Map<String, LineDto> lineMap = new HashMap<>();
        Map<String, List<SuanFaResult>> wholeRouteNameMap = new HashMap<>();

        Map<String, List<SuanFaResult>> orderByResult = result.stream().filter(s -> s.getCrosspointSort() != null).collect(Collectors.groupingBy(m -> m.getBatchNo() + "_" + m.getCarNum()));
        for (Map.Entry<String, List<SuanFaResult>> entry : orderByResult.entrySet()) {
            String batchCar = entry.getKey();
            List<SuanFaResult> taskList = entry.getValue();
            taskList = taskList.stream().sorted(Comparator.comparing(SuanFaResult::getCrosspointSort)).collect(Collectors.toList());
            SuanFaResult max = taskList.get(taskList.size() - 1);

            String lineName = max.getReceiverProvinceName() + max.getReceiverCityName() + max.getReceiverDistrictName();
            this.addLine(lineMap, lineName);
            StringBuffer resultStr = new StringBuffer("批次:" + batchCar + "\n线路:" + lineName);

            // 节点路由
            List<String> routeNameList = new ArrayList<>();
            routeNameList.add(QISHI_CANG);
            // 筛选路由
            int size = taskList.size();
            for (int i = 0; i < size; i++) {
                SuanFaResult suanFaResult = taskList.get(i);
                String receiverDistrictName = suanFaResult.getReceiverProvinceName() + suanFaResult.getReceiverCityName() + suanFaResult.getReceiverDistrictName();
                // 设置线路名称
                suanFaResult.setLineName(lineName);
                // 路由的前一段与当前段一致，则省略
                String preRouteName = routeNameList.get(routeNameList.size() - 1);
                if (!receiverDistrictName.equals(preRouteName)) {
                    routeNameList.add(receiverDistrictName);
                    continue;
                }

            }
            String wholeRouteName = String.join(TO, routeNameList);
            List<SuanFaResult> suanFaResultList = wholeRouteNameMap.get(wholeRouteName);
            if (CollectionUtils.isEmpty(suanFaResultList)) {
                suanFaResultList = new ArrayList<>();
                wholeRouteNameMap.put(wholeRouteName, suanFaResultList);
            }
            // 缓存整线路由-运单
            suanFaResultList.addAll(taskList);
        }

        // 导出线路
        ArrayList<Map<String, Object>> lineList = new ArrayList<>();
        for (Map.Entry<String, LineDto> lineDtoEntry : lineMap.entrySet()) {
            Map<String, Object> row = new LinkedHashMap<>();
            row.put("线路", "三水区 -> " + lineDtoEntry.getKey());
            row.put("车次", lineDtoEntry.getValue().getSum());
            lineList.add(row);
        }
        exportExcel(lineList, "线路");
        // 导出路由明细
        ArrayList<Map<String, Object>> routeList = new ArrayList<>();
        for (Map.Entry<String, List<SuanFaResult>> entry : wholeRouteNameMap.entrySet()) {
            String wholeRouteName = entry.getKey();
            List<SuanFaResult> suanFaResultList = entry.getValue();

            for (SuanFaResult suanFaResult : suanFaResultList) {
                Map<String, Object> row = new LinkedHashMap<>();
                row.put("线路", QISHI_CANG.concat(" -> ") + suanFaResult.getLineName());
                row.put("路由", wholeRouteName);
                row.put("线路里程", suanFaResult.getMileage());
                row.put("线路方量", suanFaResult.getLineVolume());
                row.put("运单方量", suanFaResult.getTaskVolume());
                row.put("运单号", suanFaResult.getTaskNo());
                row.put("车型", suanFaResult.getCarType());
                row.put("批次", suanFaResult.getBatchNo());
                row.put("车次", suanFaResult.getCarNum());
                routeList.add(row);
            }
        }
        exportExcel(routeList, "路由明细");
    }

    @Test
    public void 导入导出() throws SQLException {
        // 16 21 25 28 30
        String dateStr = "1031.xlsx";
        String srcfile = SRC.concat(dateStr);
        Console.log("导入原始数据-" + srcfile);
        this.inSrc(srcfile);
        String distFile = DIST.concat(dateStr);
        Console.log("导入结果-" + distFile);
        this.inResult(distFile);

    }


    public static BigDecimal toBigDecimal(String val) {
        return new BigDecimal(val).setScale(8, RoundingMode.HALF_UP);
    }

    private void exportData(Map<String, LineDto> lineMap, Map<String, RouteDto> routeMap) {
        ArrayList<Map<String, Object>> lineList = new ArrayList<>();
        for (Map.Entry<String, LineDto> lineDtoEntry : lineMap.entrySet()) {
            Map<String, Object> row = new LinkedHashMap<>();
            row.put("线路", "三水区 -> " + lineDtoEntry.getKey());
            row.put("车次", lineDtoEntry.getValue().getSum());
            lineList.add(row);
        }
        exportExcel(lineList, "线路");

        ArrayList<Map<String, Object>> routeList = new ArrayList<>();
        for (Map.Entry<String, RouteDto> routeDtoEntry : routeMap.entrySet()) {
            Console.log(routeDtoEntry.getKey() + "-" + routeDtoEntry.getValue().getSum());
            Map<String, Object> row = new LinkedHashMap<>();
            RouteDto routeDto = routeDtoEntry.getValue();
            row.put("线路", "三水区 -> " + routeDto.getLineName());
            row.put("路由", routeDtoEntry.getKey());
            row.put("车次", routeDto.getSum());
            routeList.add(row);
        }
        exportExcel(routeList, "路由");
    }

    public static void exportExcel(ArrayList<Map<String, Object>> dataList, String sheet) {
        ZipSecureFile.setMinInflateRatio(-1.0d);
        ExcelWriter writer = new ExcelWriter(OUT_EXCEL, sheet);
        writer.write(dataList, true);
        writer.close();
    }

    private void addRoute(Map<String, RouteDto> routeMap, String routeName, String lineName) {
        if (routeMap.containsKey(routeName)) {
            RouteDto routeDto = routeMap.get(routeName);
            routeDto.setSum(routeDto.getSum() + 1);
        } else {
            RouteDto routeDto = new RouteDto();
            routeDto.setSum(1);
            routeDto.setLineName(lineName);
            routeMap.put(routeName, routeDto);
        }
    }

    private void addLine(Map<String, LineDto> lineMap, String lineName) {
        if (lineMap.containsKey(lineName)) {
            LineDto line = lineMap.get(lineName);
            line.setSum(line.getSum() + 1);
        } else {
            LineDto line = new LineDto();
            line.setSum(1);
            lineMap.put(lineName, line);
        }
    }


}
